from fastapi import HTTPException  # 导入HTTP异常类
from sqlalchemy.orm import Session  # 导入数据库会话
from .score_models import Score  # 导入成绩模型
from .score_schema import Score_schema  # 导入成绩数据验证模型
from xiangmu.student.stu_crud import get_id_stu  # 导入学生查询工具
from sqlalchemy import func  # 导入SQL函数
from xiangmu.student.stu_models import Stu  # 导入学生模型（用于关联查询）


# 添加成绩记录
def add_score(db: Session, score_schema: Score_schema):
    """
    添加学生考核成绩，自动同步学生姓名和班级（从学生表）
    :param db: 数据库会话
    :param score_schema: 成绩数据模型
    :return: 新增的成绩记录或None（若已存在）
    """
    # 检查该学生的该次考试成绩是否已存在（复合主键：student_id + exam_sequence）
    existing = db.query(Score).filter(
        (Score.student_id == score_schema.student_id) &
        (Score.exam_sequence == score_schema.exam_sequence)
    ).first()
    if existing:
        return None

    # 从学生表查询学生信息，确保学生存在
    student = get_id_stu(db, score_schema.student_id)
    if not student:
        raise HTTPException(status_code=404, detail="学生不存在")

    # 创建成绩记录（同步学生表的姓名和班级作为冗余字段）
    db_score = Score(
        student_id=score_schema.student_id,
        student_name=student.student_name,  # 从学生表同步姓名
        class_name=student.class_name,      # 从学生表同步班级
        exam_sequence=score_schema.exam_sequence,
        score=score_schema.score
    )
    db.add(db_score)
    db.commit()
    db.refresh(db_score)
    return db_score


# 修改成绩记录
def mod_score(db: Session, mod_schema: Score_schema):
    """
    修改学生的考核成绩（通过student_id和exam_sequence定位记录）
    :return: 更新后的成绩记录或None（若不存在）
    """
    # 查找要修改的记录（复合主键定位）
    db_score = db.query(Score).filter(
        (Score.student_id == mod_schema.student_id) &
        (Score.exam_sequence == mod_schema.exam_sequence)
    ).first()

    if not db_score:
        return None  # 记录不存在

    # 仅更新提供的非空字段
    update_data = mod_schema.dict(exclude_unset=True)
    for key, value in update_data.items():
        setattr(db_score, key, value)  # 动态更新字段值

    db.commit()
    db.refresh(db_score)
    return db_score


# 删除成绩记录
def del_score(db: Session, student_id: int, exam_sequence: int):
    """
    删除学生的某次考核成绩
    :param student_id: 学生编号
    :param exam_sequence: 考试序号
    :return: 删除的记录或None（若不存在）
    """
    # 查找要删除的记录
    db_score = db.query(Score).filter(
        (Score.student_id == student_id) &
        (Score.exam_sequence == exam_sequence)
    ).first()

    if not db_score:
        return None  # 记录不存在

    db.delete(db_score)
    db.commit()
    return db_score


# 查询所有考试成绩均在80分以上的学生
def get_stu_all_above_80(db: Session):
    """
    筛选出所有考试成绩均≥80分的学生，并返回其成绩详情
    逻辑：按学生分组，筛选最小成绩>80的学生（若最小成绩都>80，则所有成绩均达标）
    """
    # 子查询：筛选出所有考试成绩均>80的学生ID
    # 1. 按学生ID分组（group_by）
    # 2. 用having子句保留"最小成绩>80"的组（确保该学生所有成绩都达标）
    # 3. subquery()将结果转为子查询，供后续关联使用
    qualified_stu = db.query(
        Score.student_id  # 只需要学生ID用于关联
    ).group_by(Score.student_id).having(func.min(Score.score) > 80).subquery()

    # 关联查询：获取学生信息 + 对应的成绩详情
    result = db.query(
        Stu.student_id,        # 学生编号
        Stu.student_name,      # 学生姓名
        Score.exam_sequence,   # 考试次数/序号
        Score.score            # 成绩
    ).join(
        qualified_stu,         # 关联子查询（只保留符合条件的学生）
        Stu.student_id == qualified_stu.c.student_id  # 关联条件：学生ID匹配
    ).join(
        Score,                 # 关联成绩表，获取具体成绩记录
        Stu.student_id == Score.student_id  # 关联条件：学生ID匹配
    ).all()  # 执行查询，返回所有结果

    # 转换为字典列表返回（更易读的格式）
    return [
        {"学生编号": id, "姓名": name, "考试次数": seq, "成绩": score}
        for id, name, seq, score in result
    ]


# 查询有2次以上不及格（<60）的学生
def get_stu_more_than_two_fail(db: Session):
    """
    筛选出不及格（<60分）次数超过2次的学生，并返回其不及格记录
    """
    # 子查询1：筛选所有不及格的成绩记录（分数<60）
    fail_scores = db.query(
        Score.student_id,       # 学生ID
        Score.exam_sequence,    # 考试次数
        Score.score             # 不及格成绩
    ).filter(Score.score < 60).subquery()  # 过滤条件：成绩<60

    # 子查询2：从不及格记录中筛选出"不及格次数>2"的学生ID
    # 1. 按学生ID分组（统计每个学生的不及格次数）
    # 2. having子句保留"次数>2"的学生
    fail_stu = db.query(
        fail_scores.c.student_id  # 子查询的字段需用.c访问
    ).group_by(fail_scores.c.student_id).having(func.count() > 2).subquery()

    # 关联查询：获取学生基本信息 + 对应的不及格记录
    result = db.query(
        Stu.student_name,        # 学生姓名
        Stu.class_name,          # 学生班级
        fail_scores.c.exam_sequence,  # 不及格的考试次数
        fail_scores.c.score           # 不及格的成绩
    ).join(
        fail_stu,                # 关联子查询2（只保留不及格次数>2的学生）
        Stu.student_id == fail_stu.c.student_id
    ).join(
        fail_scores,             # 关联子查询1（获取这些学生的具体不及格记录）
        Stu.student_id == fail_scores.c.student_id
    ).all()  # 执行查询

    # 转换为字典列表返回
    return [
        {"姓名": name, "班级": cls, "考试次数": seq, "不及格成绩": score}
        for name, cls, seq, score in result
    ]


# 统计每次考试每个班级的平均分
def get_class_avg_per_exam(db: Session):
    """
    按考试序号和班级分组，计算每个班级在每次考试中的平均分
    并按考试序号和平均分（降序）排序
    """
    result = db.query(
        Score.exam_sequence,                  # 考试序号（用于区分不同考试）
        Score.class_name,                     # 班级名称
        func.avg(Score.score).label("avg_score")  # 计算平均分，并起别名avg_score
    ).group_by(
        Score.exam_sequence,  # 先按考试序号分组（同一考试内统计）
        Score.class_name      # 再按班级分组（同一考试的不同班级）
    ).order_by(
        Score.exam_sequence,  # 先按考试序号升序排列
        func.avg(Score.score).desc()  # 同一考试内按平均分降序排列（高分班级在前）
    ).all()  # 执行查询

    # 转换为字典列表，平均分保留两位小数
    return [{"考试次数": seq, "班级": cls, "平均分": round(avg, 2)}
            for seq, cls, avg in result
            ]


